Imports System.Reflection
Imports System.Drawing
Imports System.IO
Imports System.Windows.Forms
Imports System.Threading
Imports System.Xml
Imports PIA = Microsoft.Office.Interop.Excel
Imports System.Data.OleDb
Imports Microsoft.Office.Interop

Public Class ExporImporExcel

    Dim mi_conexion As String
    Dim xobj As System.Windows.Forms.OpenFileDialog
    Dim nHoja As String
    Dim m_Excel = New PIA.Application

    Public Function exportExcelXML(ByVal pDataTable As DataSet, ByVal sNombre As String) As String

        If pDataTable.Tables.Count = 0 Then
            Return Nothing
            Exit Function
        End If

        Dim archivo As String = sNombre

        Dim Excel As New Microsoft.Office.Interop.Excel.ApplicationClass

        Dim vFormato As PIA.XlRangeAutoFormat
        'Dim valor As Integer

        Dim vCultura As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture

        'Es importante definirle la cultura al sistema
        'ya que podria generar errores


        vFormato = PIA.XlRangeAutoFormat.xlRangeAutoFormatSimple

        With Excel

            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

            If pDataTable.Tables.Count > 0 Then
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()

                Dim t As Integer = 1
                For Each tabla As DataTable In pDataTable.Tables
                    If tabla.Rows.Count > 0 Then
                        If t > .Worksheets.Count Then
                            .Worksheets.Add()
                        End If

                        .Worksheets(1).Select()


                        Dim i As Integer = 1
                        For col As Integer = 0 To tabla.Columns.Count - 1
                            .Cells(1, i).value = tabla.Columns(col).ColumnName
                            .Cells(1, i).EntireRow.Font.Bold = True
                            i += 1
                        Next

                        i = 2

                        Dim k As Integer = 1
                        For col As Integer = 0 To tabla.Columns.Count - 1
                            i = 2
                            For row As Integer = 0 To tabla.Rows.Count - 1
                                .Cells(i, k).Value = tabla.Rows(row).ItemArray(col)
                                i += 1
                            Next
                            k += 1
                        Next
                        t += 1

                        .ActiveCell.Worksheet.Name = tabla.TableName

                        .ActiveCell.Worksheet.Range(.ActiveCell.Worksheet.Cells(1, 1), .ActiveCell.Worksheet.Cells(.ActiveCell.Worksheet.UsedRange.Rows.Count, .ActiveCell.Worksheet.UsedRange.Columns.Count)).AutoFormat(vFormato)
                    End If
                Next

                'Dim forma As New PIA.XlFileFormat
                'forma = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8


                .ActiveCell.Worksheet.SaveAs(archivo, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8)
            End If
        End With

        Excel.Quit()
        Excel = Nothing

        Dim procesos() As Process = Process.GetProcessesByName("EXCEL")
        For Each proceso As Process In procesos
            proceso.Kill()
        Next

        System.Threading.Thread.CurrentThread.CurrentCulture = vCultura

        Return archivo
    End Function

    Public Sub exportExcelDT(ByVal pDataTable As DataSet, ByVal sNombre As String, ByVal ruta As String)

        Dim vFileName(0) As String '= ruta & "\Poliza_" & sNombre & ".tmp"
        Dim dFilename As String = Nothing

        dFilename = ruta & "\Poliza_" & sNombre & ".xls"

        ' FileOpen(1, vFileName, OpenMode.Output)

        Dim nArchivo As Integer = 0
        For Each pTabla As DataTable In pDataTable.Tables
            ReDim Preserve vFileName(nArchivo)
            vFileName(nArchivo) = ruta & "\" & sNombre & "(" & pTabla.TableName & ").tmp"

            Dim tmpTable As New DataTable
            tmpTable = pTabla.Clone

            For Each fila As DataRow In pTabla.Rows
                tmpTable.ImportRow(fila)
            Next

            'tmpTable.Columns.RemoveAt(0)
            'tmpTable.Columns.RemoveAt(0)

            Dim sb As String = ""
            Dim dc As DataColumn
            For Each dc In tmpTable.Columns
                sb &= dc.Caption & vbTab
            Next


            sb += vbCrLf
            'sb = sb.Replace(",", vbTab)
            'PrintLine(nArchivo, sb)
            Dim i As Integer = 0
            Dim dr As DataRow
            For Each dr In tmpTable.Rows
                i = 0 ': sb = ""
                For Each dc In tmpTable.Columns
                    If Not IsDBNull(dr(i)) Then
                        sb &= CStr(dr(i)) & ","
                    Else
                        sb &= ","
                    End If
                    i += 1
                Next
                sb = sb.Replace(",", vbTab)
                sb += vbCrLf
                '    PrintLine(nArchivo, sb)
            Next
            'FileClose(nArchivo)
            Crea_Documento(sb, vFileName(nArchivo))

            nArchivo += 1
        Next

        TextToExcel(vFileName, dFilename)
    End Sub

    Public Sub TextToExcel(ByVal pFileName() As String, ByVal dFileName As String)
        Dim vFormato As PIA.XlRangeAutoFormat
        'Dim valor As Integer

        Dim vCultura As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture

        'Es importante definirle la cultura al sistema
        'ya que podria generar errores
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")


        Dim Exc As PIA.Application = New PIA.Application

        Dim Excel As PIA.Application = New PIA.Application
        'Excel.NewWorkbook.Add(dFileName)
        Excel.Workbooks.Add()
        'Excel.Workbooks(1).Worksheets.Delete()

        For Each pFile As String In pFileName
     

            Exc.Workbooks.OpenText(pFile, , , , PIA.XlTextQualifier.xlTextQualifierNone, , True)

            Dim Wb As PIA.Workbook = Exc.ActiveWorkbook
            Dim Ws As PIA.Worksheet = Wb.ActiveSheet

            vFormato = PIA.XlRangeAutoFormat.xlRangeAutoFormatSimple
            'En el ejemplo vienen otros formatos posibles

            Ws.Range(Ws.Cells(1, 1), Ws.Cells(Ws.UsedRange.Rows.Count, Ws.UsedRange.Columns.Count)).AutoFormat(vFormato)
            'pFileName = pFileName.Replace(".tmp", ".xls")

            'Excel.Workbooks(1).Worksheets.Add()
            'Excel.Worksheets.Add(Ws)
        Next




        'pFileName = Path.GetTempFileName.Replace("tmp", System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop))
        'System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)

        'File.Delete(pFileName)

        Excel.ActiveWorkbook.SaveAs(dFileName, PIA.XlTextQualifier.xlTextQualifierNone - 1)

        Exc.Quit()

        'Ws = Nothing
        'Wb = Nothing
        'Exc = Nothing

        'GC.Collect()
        'If valor > -1 Then
        '    Dim p As System.Diagnostics.Process = New System.Diagnostics.Process
        '    p.EnableRaisingEvents = False
        '    Process.Start("Excel.exe", pFileName)
        'End If
        'System.Threading.Thread.CurrentThread.CurrentCulture = vCultura
        File.Delete(dFileName & ".tmp")
    End Sub

    Private Function HojasExcel(ByVal sArchivo As String) As DataTable
        m_Excel = New PIA.Application
        'Dim libro As PIA.Workbook
        Dim hoja As PIA.Worksheet
        Dim dtHojas As New DataTable

        If sArchivo.Length > 0 Then
            m_Excel.Workbooks.Open(sArchivo)
            m_Excel.Visible = False

            dtHojas.Columns.Add("id_hoja")
            dtHojas.Columns.Add("sHoja")

            For i As Integer = 0 To m_Excel.Worksheets.Count - 1
                hoja = m_Excel.Worksheets(i + 1)
                dtHojas.Rows.Add(i, hoja.Name)
            Next
        Else
        End If
        Return dtHojas
    End Function

    Private Function NomPropio(ByVal sTexto As String) As String
        Dim sNomP As String
        sNomP = StrConv(sTexto, VbStrConv.ProperCase)
        Return sNomP
    End Function

    Private Sub Crea_Documento(ByVal texto As String, ByVal ruta As String)
        If IO.File.Exists(ruta) Then
            IO.File.Delete(ruta)
        End If

        Dim oWrite As System.IO.StreamWriter
        oWrite = IO.File.CreateText(ruta)
        For Each linea As String In texto.Split(vbCrLf)
            oWrite.WriteLine(linea)
        Next
        oWrite.Close()
    End Sub

End Class
